/* WorldExpCalJT.java
 *
 * Project/Product: GW
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   Administrator	2009-1-8	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2009
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package system.base;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import system.LoginContext;

import framework.core.DBConnection;
import framework.core.DBEngine;
import framework.core.DBResult;
import framework.core.DateUtil;

public class WorldExpCal {
    private String busType;
    
    private int fNum; 
    private int fLjNum;
    private double fPrice;
    private double fLjPrice;
    
    private int wNum;
    private double wPrice;
    private int wLjNum;
    private double wLjPrice;
    
    private int nNum;
    private double nPrice;
    private int nLjNum;
    private double nLjPrice;
    /**
     * @return the warehouseCode
     */
    public String getBusType() {
        return this.busType;
    }
    /**
     * @param aWarehouseCode the warehouseCode to set
     */
    public void setBusType(String busType) {
        this.busType = busType;
    }
    /**
     * @return the fNum
     */
    public int getFNum() {
        return this.fNum;
    }
    /**
     * @param aNum the fNum to set
     */
    public void setFNum(int aNum) {
        this.fNum = aNum;
    }
    /**
     * @return the fLjNum
     */
    public int getFLjNum() {
        return this.fLjNum;
    }
    /**
     * @param aLjNum the fLjNum to set
     */
    public void setFLjNum(int aLjNum) {
        this.fLjNum = aLjNum;
    }
    /**
     * @return the fPrice
     */
    public double getFPrice() {
        return this.fPrice;
    }
    /**
     * @param aPrice the fPrice to set
     */
    public void setFPrice(double aPrice) {
        this.fPrice = aPrice;
    }
    /**
     * @return the fLjPrice
     */
    public double getFLjPrice() {
        return this.fLjPrice;
    }
    /**
     * @param aLjPrice the fLjPrice to set
     */
    public void setFLjPrice(double aLjPrice) {
        this.fLjPrice = aLjPrice;
    }
    /**
     * @return the wNum
     */
    public int getWNum() {
        return this.wNum;
    }
    /**
     * @param aNum the wNum to set
     */
    public void setWNum(int aNum) {
        this.wNum = aNum;
    }
    /**
     * @return the wPrice
     */
    public double getWPrice() {
        return this.wPrice;
    }
    /**
     * @param aPrice the wPrice to set
     */
    public void setWPrice(double aPrice) {
        this.wPrice = aPrice;
    }
    /**
     * @return the wLjNum
     */
    public int getWLjNum() {
        return this.wLjNum;
    }
    /**
     * @param aLjNum the wLjNum to set
     */
    public void setWLjNum(int aLjNum) {
        this.wLjNum = aLjNum;
    }
    /**
     * @return the wLjPrice
     */
    public double getWLjPrice() {
        return this.wLjPrice;
    }
    /**
     * @param aLjPrice the wLjPrice to set
     */
    public void setWLjPrice(double aLjPrice) {
        this.wLjPrice = aLjPrice;
    }
    /**
     * @return the nNum
     */
    public int getNNum() {
        return this.nNum;
    }
    /**
     * @param aNum the nNum to set
     */
    public void setNNum(int aNum) {
        this.nNum = aNum;
    }
    /**
     * @return the nPrice
     */
    public double getNPrice() {
        return this.nPrice;
    }
    /**
     * @param aPrice the nPrice to set
     */
    public void setNPrice(double aPrice) {
        this.nPrice = aPrice;
    }
    /**
     * @return the nLjNum
     */
    public int getNLjNum() {
        return this.nLjNum;
    }
    /**
     * @param aLjNum the nLjNum to set
     */
    public void setNLjNum(int aLjNum) {
        this.nLjNum = aLjNum;
    }
    /**
     * @return the nLjPrice
     */
    public double getNLjPrice() {
        return this.nLjPrice;
    }
    /**
     * @param aLjPrice the nLjPrice to set
     */
    public void setNLjPrice(double aLjPrice) {
        this.nLjPrice = aLjPrice;
    }
    
    public static WorldExpCal fetch(ResultSet rs) throws Exception{
        WorldExpCal obj = new WorldExpCal();
        if(rs == null) return obj;
        
        obj.setBusType(rs.getString("BUS_TYPE"));
        
        obj.setFLjNum(rs.getInt("F_LJ_NUM"));
        obj.setFLjPrice(rs.getDouble("F_LJ_PRICE"));
        obj.setFNum(rs.getInt("F_NUM"));
        obj.setFPrice(rs.getDouble("F_PRICE"));
        
        obj.setWLjNum(rs.getInt("W_LJ_NUM"));
        obj.setWLjPrice(rs.getDouble("W_LJ_PRICE"));
        obj.setWNum(rs.getInt("W_NUM"));
        obj.setWPrice(rs.getDouble("W_PRICE"));
        
        obj.setNLjNum(rs.getInt("N_LJ_NUM"));
        obj.setNLjPrice(rs.getDouble("N_LJ_PRICE"));
        obj.setNNum(rs.getInt("N_NUM"));
        obj.setNPrice(rs.getDouble("N_PRICE"));
        
        return obj;
    }
    
    public static List calData(DBConnection con, LoginContext loginInfo) throws Exception{
        List list = new ArrayList();
        
        String warehouseCode = loginInfo.getWarehouse().getCode();
        String warehouseJtCode = WorldExp.changeWarehouseJTCode(warehouseCode);
        String currentDate = new DateUtil().getFullString();
        String year = currentDate.substring(0, 4);
        String year_month = currentDate.substring(0, 7);
        String year_month_day = currentDate.substring(0, 10);
        
       
        //当月发动机数
        String sql_f = "select bus_type, count(*) f_num, sum(material_price) f_price, " +
                          "0 f_lj_num, 0 f_lj_price, 0 w_num, 0 w_price, 0 w_lj_num, 0 w_lj_price, " +
                          "0 n_num, 0 n_price, 0 n_lj_num, 0 n_lj_price from t_world_exp " +
                          "where bill_type_code = '01' and out_date like '" + year_month + "%' " +
                          "and warehouse_code = '" + warehouseJtCode + "' group by bus_type ";
        //累计发动机数
        String sql_f_lj = "select bus_type, 0 f_num, 0 f_price, count(*) f_lj_num, " +
                            "sum(material_price) f_lj_price, 0 w_num, 0 w_price, 0 w_lj_num, " +
                            "0 w_lj_price, 0 n_num, 0 n_price, 0 n_lj_num, 0 n_lj_price " +
                            "from t_world_exp where bill_type_code = '01' " + 
                            "and out_date < '" + year_month + "a' " +
                            "and warehouse_code = '" + warehouseJtCode + "' group by bus_type ";
        
        //当月外车身数
        String sql_w = "select bus_type, 0 f_num, 0 f_price, 0 f_lj_num, 0 f_lj_price, " +
                       "count(*) w_num, sum(material_price) w_price, 0 w_lj_num, 0 w_lj_price, " +
                       "0 n_num, 0 n_price, 0 n_lj_num, 0 n_lj_price from t_world_exp " +
                       "where bill_type_code = '02' and out_date like '" + year_month + "%' " +
                       "and warehouse_code = '" + warehouseJtCode + "' group by bus_type";
        
        //累计外车身数
        String sql_w_lj = "select bus_type, 0 f_num, 0 f_price, 0 f_lj_num, 0 f_lj_price, " +
                          "0 w_num, 0 w_price, count(*) w_lj_num, sum(material_price) w_lj_price, " +
                          "0 n_num, 0 n_price, 0 n_lj_num, 0 n_lj_price from t_world_exp " +
                          "where bill_type_code = '02' and out_date < '" + year_month + "a' " +
                          "and warehouse_code = '" + warehouseJtCode + "' group by bus_type";
        
        //当月内车身数
        String sql_n = "select bus_type, 0 f_num, 0 f_price, 0 f_lj_num, 0 f_lj_price, " +
                       "0 w_num, 0 w_price, 0 w_lj_num, 0 w_lj_price, count(*) n_num, sum(material_price) n_price, " +
                       "0 n_lj_num, 0 n_lj_price from t_world_exp where bill_type_code = '03' " +
                       "and out_date like '" + year_month + "%' " + "and warehouse_code = '" + warehouseJtCode + "' group by bus_type ";
        
        //累计内车身数
        String sql_n_fj = "select bus_type, 0 f_num, 0 f_price, 0 f_lj_num, 0 f_lj_price, " +
                          "0 w_num, 0 w_price, 0 w_lj_num, 0 w_lj_price, 0 n_num, 0 n_price, " +
                          "count(*) n_lj_num, sum(material_price) n_lj_price from t_world_exp " +
                          "where bill_type_code = '03' and out_date < '" + year_month + "a' " +
                          "and warehouse_code = '" + warehouseJtCode + "' group by bus_type";
        
        //合并查询
        String sql = "select bus_type, sum(f_num) f_num, sum(f_price) f_price, " +
                     "sum(f_lj_num) f_lj_num, sum(f_lj_price) f_lj_price, sum(w_num) w_num, " +
                     "sum(w_price) w_price, sum(w_lj_num) w_lj_num, sum(w_lj_price) w_lj_price, " +
                     "sum(n_num) n_num, sum(n_price) n_price, sum(n_lj_num) n_lj_num, sum(n_lj_price) n_lj_price " +
                     " from (" + sql_f + " union all " + sql_f_lj +
                     " union all " + sql_w + " union all " + sql_w_lj + " union all " +
                     sql_n + " union all " + sql_n_fj + ") a group by a.bus_type " +
                     "order by a.bus_type ";
         
        DBResult result = null;
        try
        {
            result = DBEngine.doSelect(con, sql);
            java.sql.ResultSet rs = result.getRs();
            WorldExpCal obj = null;
            while(rs.next())
            {
                obj = WorldExpCal.fetch(rs);
                list.add(obj);
            }
        }
        finally
        {
            if(result!=null) result.clear() ;
        }
        
        return list;
    }
}
